﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Xml;
using Deeper.Common;
using System.Data.SqlClient;
using System.Transactions;


namespace Deeper.DataAccess
{

	public class DataContext : IDataContext
	{
		#region 属性
		/// <summary>
		/// 超时
		/// </summary>
		protected int TimeOut { set; get; }
		/// <summary>
		/// 当前连接字符串
		/// </summary>
		protected Connstring ConnectionString { set; get; }


		public bool TestConnect()
		{
			bool result = false;
			try
			{
				using (DbConnection dbcon = ConnectionString.CreateConnection())
				{
					dbcon.Open();
					result = true;
					dbcon.Close();
				}
			}
			catch { result = false; }
			return result;
		}


		/// <summary>
		/// 获取Object的Dbtype
		/// </summary>
		/// <param name="t"></param>
		/// <returns></returns>
		protected DbType TypeToDbType(Type t)
		{
			DbType dbt = DbType.Object;
			try
			{
				dbt = (DbType)Enum.Parse(typeof(DbType), t.Name);
			}
			catch
			{
				throw new HDbException(string.Format("类型[{0}]无法转换为数据库类型", t.ToString()));
			}
			return dbt;
		}


		#endregion

		#region 构造与析构

		public DataContext(string connectionstring, int timeOut = 0)
		{
			this.TimeOut = timeOut;
			ConnectionString = new Connstring
			{
				ConnectionString = connectionstring,
				ProviderName = "System.Data.SqlClient"
			};
		}

		public DataContext(string path = null, string name = null, int timeOut = 0)
		{
			if (name.IsNull()) name = "mssql";
			this.TimeOut = timeOut;
			ConnectionString = new Connstring(name, path);
		}



		#endregion
		protected bool IsUserTran { set; get; }
		protected DbTransaction CurrentTran { set; get; }

		#region 事务处理
		/// <summary>
		/// 创建事务并启用
		/// </summary>
		public void BeginTran(System.Data.IsolationLevel Level = System.Data.IsolationLevel.ReadUncommitted)
		{
			if (!IsUserTran)
			{
				DbConnection conn = ConnectionString.CreateConnection();
				CurrentTran = conn.BeginTransaction(Level);
				IsUserTran = true;
			}
		}

		private void DisposeTran(Action<DbTransaction> act)
		{
			if (CurrentTran.IsNotNull())
			{
				act(CurrentTran);
				CurrentTran.Connection.Close();
				CurrentTran.Connection.Dispose();
				CurrentTran.Dispose();
				CurrentTran = null;
				IsUserTran = false;
			}
		}


		/// <summary>
		/// 提交事务
		/// </summary>
		public void Commit()
		{
			DisposeTran((s) => s.Commit());
		}

		/// <summary>
		/// 回滚事务
		/// </summary>
		public void RollBack()
		{
			DisposeTran((s) => s.Rollback());
		}
		#endregion

		#region 公共方法




		/// <summary>
		/// 创建command
		/// </summary>
		/// <param name="commandText"></param>
		/// <param name="parameters"></param>
		/// <returns></returns>
		protected DbCommand BuildCommand(string commandText, DbConnection conn, MyDbParams[] parameters)
		{
			DbCommand command = conn.CreateCommand();// new SqlCommand(commandText, conn);
			command.CommandText = commandText;
			command.CommandTimeout = this.TimeOut;
			if (parameters != null)
			{
				foreach (MyDbParams parameter in parameters)
				{
					///若传入值未定义，则赋值为空
					if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
						parameter.Value = DBNull.Value;

					///根据参数传入类型，添加参数到command中
					DbParameter sp = command.CreateParameter();
					sp.ParameterName = parameter.ParameterName;
					sp.DbType = parameter.DbType;
					sp.Size = parameter.Size;
					sp.Value = parameter.Value;
					sp.Direction = parameter.Direction;
					sp.SourceVersion = DataRowVersion.Default;
					sp.SourceColumn = string.Empty;
					command.Parameters.Add(sp);

				}
			}
			return command;
		}

		/// <summary>
		/// 返回参数
		/// </summary>
		/// <param name="command"></param>
		/// <param name="cmdParms"></param>
		protected void ReturnParameterValue(DbCommand command, ref MyDbParams[] cmdParms)
		{
			for (int i = 0; i < cmdParms.Length; i++)
			{
				if (cmdParms[i].Direction != ParameterDirection.Input)
				{
					cmdParms[i].Value = command.Parameters[cmdParms[i].ParameterName].Value;
				}
			}
		}

		/// <summary>
		/// 直接根据object生成command
		/// </summary>
		/// <param name="strSql"></param>
		/// <param name="parameterValues"></param>
		/// <returns></returns>
		protected DbCommand BuildParamCommand(string strSql, DbConnection conn, params object[] parameterValues)
		{
			string[] parameters = new string[parameterValues.Length];

			for (int i = 0; i < parameterValues.Length; i++)
			{
				parameters[i] = "@p" + i.ToString();
			}

			DbCommand dbCommand = conn.CreateCommand();
			dbCommand.CommandText = string.Format(strSql, parameters);//把{0}之类的参数，转换成params的名字
			dbCommand.CommandTimeout = TimeOut;
			for (int i = 0; i < parameterValues.Length; i++)
			{
				if (parameterValues[i].IsNull())
				{
					DbParameter p = dbCommand.CreateParameter();
					p.ParameterName = "p" + i.ToString();
					p.DbType = DbType.Int32;
					p.Value = null;
					dbCommand.Parameters.Add(p);
				}
				else
				{
					DbParameter p = dbCommand.CreateParameter();
					p.ParameterName = "p" + i.ToString();
					p.DbType = TypeToDbType(parameterValues[i].GetType());
					p.Value = parameterValues[i];
					dbCommand.Parameters.Add(p);
				}
			}
			return dbCommand;
		}


		protected DataSet GetDataset(DbCommand dbCommand)
		{
			using (DbDataAdapter da = ConnectionString.Factory.Value.CreateDataAdapter())
			{
				da.SelectCommand = dbCommand;
				DataSet ds = new DataSet();
				da.Fill(ds);
				return ds;
			}
		}

		#endregion

		#region 数据库操作

		/// <summary>
		/// 获取Sequence
		/// </summary>
		/// <param name="tableName"></param>
		/// <returns></returns>
		public int GetSequenceSql(string tableName)
		{
			return GetSingle<int>("exec sp_GetSequence {0}", tableName);
			//if (obj == null) throw new Exception("获取Sequence失败");
			//return obj.ToString().ToInt();
		}

		protected void RunAction(Action<DbConnection> act)
		{
			DbConnection dc = IsUserTran ? CurrentTran.Connection : ConnectionString.CreateConnection();
			try
			{
				act(dc);
			}
			finally
			{
				if (!IsUserTran) dc.Dispose();
			}
		}

		/// <summary>
		/// 根据object执行sql语句
		/// </summary>
		/// <param name="strSql"></param>
		/// <param name="parameterValues"></param>
		/// <returns></returns>
		public int ExecuteSql(string strSql, params object[] parameterValues)
		{
			int result = 0;
			RunAction((sc) =>
			{
				sc.Open();
				DbCommand dbCommand = BuildParamCommand(strSql, sc, parameterValues);
				result = dbCommand.ExecuteNonQuery();

			});
			return result;
		}

		/// <summary>
		/// 根据参数执行sql语句
		/// </summary>
		/// <param name="strSql"></param>
		/// <param name="cmdParams"></param>
		/// <returns></returns>
		public int ExecuteSql(string strSql, ref MyDbParams[] cmdParams)
		{
			int result = 0;
			MyDbParams[] cmd = cmdParams.ToList().ToArray();
			RunAction((sc) =>
			{
				sc.Open();
				DbCommand dbCommand = BuildCommand(strSql, sc, cmd);
				result = dbCommand.ExecuteNonQuery();
				ReturnParameterValue(dbCommand, ref cmd);
			});
			for (int i = 0; i < cmd.Count(); i++) cmdParams[i] = cmd[i];
			return result;

		}

		/// <summary>
		/// 根据object执行获取单个值的语句
		/// </summary>
		/// <param name="strSql"></param>
		/// <param name="paramterValues"></param>
		/// <returns></returns>
		public T GetSingle<T>(string strSql, params object[] paramterValues) where T : new()
		{
			T result = new T();
			RunAction(sc =>
			{
				sc.Open();
				DbCommand dbCommand = BuildParamCommand(strSql, sc, paramterValues);
				result = (T)dbCommand.ExecuteScalar();
			});
			return result;
		}



		/// <summary>
		/// 根据参数执行获取单个值的语句
		/// </summary>
		public T GetSingle<T>(string strSql, ref MyDbParams[] cmdParams) where T : new()
		{
			T result = new T();
			MyDbParams[] cmd = cmdParams.ToList().ToArray();
			RunAction(sc =>
			{
				sc.Open();
				DbCommand dbCommand = BuildCommand(strSql, sc, cmd);
				object obj = dbCommand.ExecuteScalar();
				ReturnParameterValue(dbCommand, ref cmd);
			});
			for (int i = 0; i < cmd.Count(); i++) cmdParams[i] = cmd[i];
			return result;
		}




		public DataSet GetDataset(string strSql, string where = null, string orderBy = null, params object[] paramterValues)
		{
			string sqlText = string.Empty;
			if (where.IsNotNull() && orderBy.IsNotNull()) sqlText = string.Format("{0} order by {1}", where, orderBy);
			else if (where.IsNotNull() && orderBy.IsNull()) sqlText = where;
			else if (where.IsNull() && orderBy.IsNotNull()) sqlText = string.Format("1=1 order by {0}", orderBy);
			if (sqlText.IsNotNull()) sqlText = strSql + " where " + sqlText;
			else sqlText = strSql;
			DataSet result = null;
			RunAction(sc =>
			{
				sc.Open();
				DbCommand dbCommand = BuildParamCommand(sqlText, sc, paramterValues);
				result = GetDataset(dbCommand);
			});
			return result;
		}


		#endregion

		#region 给力的操作
		/// <summary>
		/// 根据自定义语句获取记录总数
		/// </summary>
		/// <param name="db"></param>
		/// <param name="sql"></param>
		/// <param name="param"></param>
		/// <returns></returns>
		public int GetCountBySql(string sql, params object[] param)
		{
			return param.Count() > 0 ? GetSingle<int>(sql, param) : GetSingle<int>(sql);
		}


		/// <summary>
		/// 自定义分页sql2k
		/// </summary>
		/// <param name="pageIndex"></param>
		/// <param name="pageSize"></param>
		/// <param name="strSql"></param>
		/// <param name="where"></param>
		/// <param name="orderby"></param>
		/// <param name="db"></param>
		/// <param name="param"></param>
		/// <returns></returns>
		public DataTable GetPageDatatableByCustom(int pageIndex, int pageSize, string strSql = null, string where = null, string orderby = null, params object[] param)
		{
			string sql;
			if (where.IsNotNull())
			{
				strSql = string.Format("({0} where {1}) as tp", strSql, where);
				where = "";
			}
			else strSql = string.Format("( {0} ) as tp", strSql);
			string field = "*";
			if (pageIndex == 1)
			{
				if (where.IsNull())
					sql = string.Format("select top {0} {1} from {2} order by {3}  ", pageSize, field, strSql, orderby);
				else
					sql = string.Format("select top {0} {1} from {2} where {3} order by {4} ", pageSize, field, strSql, where, orderby);
			}
			else
			{
				pageIndex = (pageIndex - 1) * pageSize;

				sql = string.Format("select top {0} {1} from {2} where {3} > all(select top {4} {3} from {2} order by {3} ) order by {3}  ", pageSize, field, strSql, orderby, pageIndex);

			}

			DataSet ds = (param.IsNull() || param.Count() == 0) ? GetDataset(sql) : GetDataset(sql, paramterValues: param);
			if (ds.Tables.Count > 0) return ds.Tables[0];
			return null;
		}

		/// <summary>
		/// 获取count值
		/// </summary>
		/// <param name="sql">select .. from ..部分</param>
		/// <param name="where">where 部分</param>
		/// <param name="param">参数</param>
		/// <returns>值</returns>
		public int GetCount(string sql, string where = null, params object[] param)
		{
			string strSql = where.IsNull() ? sql : string.Format("{0} where {1}", sql, where);
			return where.IsNull() ? GetSingle<int>(strSql) : GetSingle<int>(strSql, param);
		}

		/// <summary>
		/// 分页获取列表
		/// </summary>
		/// <param name="where"></param>
		/// <param name="orderby"></param>
		/// <param name="pageSize"></param>
		/// <param name="pageIndex"></param>
		/// <param name="param"></param>
		/// <returns></returns>
		public PageResult GetPageList<T>(int pageIndex, int pageSize, string where = null, string orderby = null, params object[] param) where T : class,IModel, new()
		{
			HTableAttribute currentTableInfo = DatabaseScheme.GetTableInfo(typeof(T));
			PageResult result = new PageResult();
			result.Value = GetPageDatatableByCustom(pageIndex, pageSize, currentTableInfo.sqlSelect, where, orderby, param).AsList<T>();
			result.Count = GetCount(currentTableInfo.sqlCount, where, param);
			return result;
		}

		public PageResult GetPageList<T>(int pageIndex, int pageSize, WhereClip where, string orderby = null) where T : class,IModel, new()
		{
			if (where.IsNotNull())
				return GetPageList<T>(pageIndex, pageSize, where.ToFormatsString(), orderby, where.GetObjectParameters());
			else
				return GetPageList<T>(pageIndex, pageSize, null, orderby, param: null);
		}


		/// <summary>
		/// 获取实体列表
		/// </summary>
		/// <param name="where"></param>
		/// <param name="param"></param>
		/// <returns></returns>
		public IList<T> GetList<T>(string where = null, string orderBy = null, params object[] param) where T : class,IModel, new()
		{
			string sql = string.Empty;
			HTableAttribute currentTableInfo = DatabaseScheme.GetTableInfo(typeof(T));
			DataSet ds = GetDataset(currentTableInfo.sqlSelect, where, orderBy, param);
			return (ds.IsNotNull() && ds.Tables.Count > 0) ? ds.Tables[0].AsList<T>() : new List<T>();
		}


		/// <summary>
		/// 获取单个实体
		/// </summary>
		/// <param name="where"></param>
		/// <param name="param"></param>
		/// <returns></returns>
		public T GetModel<T>(string where, params object[] param) where T : class,IModel, new()
		{
			IList<T> tb = GetList<T>(where, param: param);
			return !tb.IsNull() && tb.Count > 0 ? tb[0] : null;
		}

		/// <summary>
		/// 根据id获取实体(仅对只有一个int型主键的表有效,其它类型表将返回null)
		/// </summary>
		/// <param name="db"></param>
		/// <param name="id">主键值</param>
		/// <returns></returns>
		public T GetEntityById<T>(int id) where T : class,IModel, new()
		{
			HTableAttribute currentTableInfo = DatabaseScheme.GetTableInfo(typeof(T));
			if (currentTableInfo.IsNull() || !currentTableInfo.IsIntegerPK)
				throw new DeeperException("必须是单主键，且主键为数字型的方可使用本方法");
			string where = string.Format("{0}={{0}}", currentTableInfo.PrimaryKey[0].Field.Name);
			return GetModel<T>(where, id);
		}

		/// <summary>
		/// 根据条件删除
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="where"></param>
		/// <param name="param"></param>
		public void Delete(string tableName, string where = "", params object[] param)
		{
			string sql = where.IsNull() ? string.Format("delete {0}", tableName) : string.Format("delete {0} where {1}", tableName, where);
			if (!where.IsNull())
				ExecuteSql(sql, param.ToArray());
			else
				ExecuteSql(sql);
		}

		/// <summary>
		/// 根据id删除
		/// </summary>
		/// <param name="id"></param>
		public void Delete<T>(int id) where T : class,IModel, new()
		{
			HTableAttribute currentTableInfo = DatabaseScheme.GetTableInfo(typeof(T));
			if (currentTableInfo.IsNull() || !currentTableInfo.IsIntegerPK)
				throw new DeeperException("执行此方法删除记录，必须要单主键，且主键字段为数字型");
			string sql = string.Format("delete {0} where {1}={{0}}", currentTableInfo.TableName, currentTableInfo.PrimaryKey[0].Field.Name);
			ExecuteSql(sql, id);
		}

		/// <summary>
		/// 删除实体
		/// </summary>
		/// <param name="model"></param>
		public void Delete(IModel model)
		{
			HTableAttribute currentTableInfo = model.GetType().GetTableInfo();
			if (currentTableInfo.IsNull()) throw new DeeperException("执行此方法删除记录，必须要单主键，且主键字段为数字型");
			string sql = string.Format("delete {0} where {1}={{0}}", currentTableInfo.TableName, currentTableInfo.PrimaryKey[0].Field.Name);
			ExecuteSql(sql, model.GetPropertyValue(currentTableInfo.PrimaryKey[0].Field.Name));
		}
		/// <summary>
		/// 插入
		/// </summary>
		/// <param name="t"></param>
		/// <returns></returns>
		public IModel Insert(IModel model)
		{
			Type type = model.GetType();
			HTableAttribute currentTableInfo = DatabaseScheme.GetTableInfo(type);
			IList<MyDbParams> plist = new List<MyDbParams>();

			int pIndex = 0;
			bool isAutoSeq = currentTableInfo.PrimaryKey.Count > 0 ? currentTableInfo.PrimaryKey[0].IsIdentity : false;
			if (!isAutoSeq)
			{
				int id = GetSequenceSql(currentTableInfo.TableName);
				currentTableInfo.PrimaryKey[0].Field.SetValue(model, id, null);
			}
			currentTableInfo.Columns.Cast<HColumnAttribute>().Where(hc => !hc.IsIdentity).ToList().ForEach(
				hc => plist.Add(
					new MyDbParams
					{
						DbType = hc.ColumnDbType,
						IsNullable = hc.IsNullable,
						ParameterName = string.Format("@p{0}", pIndex++),
						Value = hc.Field.GetValue(model, null),
						Size = hc.Length
					}));


			string sql = currentTableInfo.sqlInsert;
			MyDbParams[] list = plist.ToArray();

			if (isAutoSeq)
			{
				sql += " select   @@identity   as   lastid ";
				int lastid = GetSingle<int>(sql, ref list);
				currentTableInfo.PrimaryKey[0].Field.SetValue(model, lastid, null);
			}
			else ExecuteSql(sql, ref list);
			return model;
		}
		/// <summary>
		/// 更新
		/// </summary>
		/// <param name="db"></param>
		/// <param name="t"></param>
		public IModel Update(IModel model)
		{
			Type type = model.GetType();
			HTableAttribute currentTableInfo = DatabaseScheme.GetTableInfo(type);
			IList<MyDbParams> plist = new List<MyDbParams>();
			int[] pIndex = { 0 };
			currentTableInfo.Columns.Cast<HColumnAttribute>().Where(hc => !hc.IsPrimary).ToList().ForEach(
				hc => plist.Add(
					new MyDbParams
						{
							DbType = hc.ColumnDbType,
							IsNullable = hc.IsNullable,
							ParameterName = string.Format("@p{0}", pIndex[0]++),
							Value = hc.Field.GetValue(model, null),
							Size = hc.Length
						}));

			currentTableInfo.PrimaryKey.Cast<HColumnAttribute>().ToList().ForEach(
				hc => plist.Add(
					new MyDbParams
						{
							DbType = hc.ColumnDbType,
							IsNullable = hc.IsNullable,
							ParameterName = string.Format("@p{0}", pIndex[0]++),
							Value = hc.Field.GetValue(model, null),
							Size = hc.Length
						}));
			MyDbParams[] list = plist.ToArray();
			ExecuteSql(currentTableInfo.sqlUpdate, ref list);
			return model;
		}

		/// <summary>
		/// 保存实体方法，id＝0时插入，非0时更新
		/// </summary>
		/// <param name="db"></param>
		/// <param name="t"></param>
		/// <returns></returns>
		public IModel Save(IModel model)
		{
			object obj = model;
			HTableAttribute currentTableInfo = DatabaseScheme.GetTableInfo(model.GetType());
			Validator<dynamic> result = model.CheckNullable();
			if (result.ErrorMessage.Count > 0) throw new DeeperException(result.ErrorMessageString);
			result = model.CheckUniquenese(this);
			if (result.ErrorMessage.Count > 0) throw new DeeperException(result.ErrorMessageString);

			if (!currentTableInfo.IsIntegerPK) throw new DeeperException("非单主键且主键类型不是int型的无法使用Save方法");
			int id = (int)model.GetPropertyValue(currentTableInfo.PrimaryKey[0].Field.Name);

			obj = id == 0 ? this.Insert(model) : this.Update(model);
			return obj as IModel;
		}
		#endregion
	}
}
